1. Field of the Invention
This invention relates in general to computer-implemented database systems, and more particularly to a method, apparatus and program storage device for managing buffers during online reorganization.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into tables that consist of rows and columns of data. The rows are formally called tuples. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on direct access storage devices (DASD) such as magnetic or optical disk drives for semi-permanent storage.
A table can be divided into partitions, with each partition containing a portion of the table's data. By partitioning tables, the speed and efficiency of data access can be improved. For example, partitions containing more frequently used data can be placed on faster data storage devices, and parallel processing of data can be improved by spreading partitions over different DASD volumes, with each I/O stream on a separate channel path. Partitioning also promotes high data availability, enabling application and utility activities to progress in parallel on different partitions of data.
Indexing is a technique used by most current database management systems to speed up particular kinds of queries (usually by internally generating and storing redundant information to more quickly locate table entries). An index is an ordered set of references to the records or rows in a database file or table. The index is used to access each record in the file using a key (i.e., one of the fields of the record or attributes of the row).
Over time and with frequent use, databases often become disorganized. Constant additions, deletions and updates cause data to become disorganized. When that happens, retrieving data involves extensive CPU, I/O and elapsed time, costing time, money and end-user productivity. In turn, customers are lost and revenue decreases. To reduce these costs, data must be reorganized.
Accordingly, numerous attempts have been made to reorganize such databases. Reorganization of a database includes changing some aspect of the logical and/or physical arrangement of the database. Most database management systems (DBMS's) today provide some type of reorganizer utility that provides online reorganization capability.
Reorganizing a database can require significant time and usually involves taking the database offline. Reorganization of table spaces places data rows in clustering order and redistributes the rows on pages to optimize performance. With an online reorganization technique, where applications may access and update the data during the reorganization process, data rows may be unloaded from the source and reorganized into a “shadow” target table space partition(s) and index(es).
When online reorganization copies rows from the source table space and indexes into a shadow target, changes to the original rows occurring after being copied to the shadows, must be captured. This process occurs by reading changes from the log, and then applying them against the shadow objects.
In existing implementations, e.g., DB2 for z/OS, the log apply phase is a bit different for table space log records and index log records. An enhancement to the existing implementation allows multiple tasks for applying the log when reorganizing a subset of table space partitions. There is one log apply task for the table space partitions, and there can also be one log apply task for each NPI. The log phase uses two buffers for reading and applying log records for the table space partitions and non-partitioned indexes. While one buffer is being filled by the log read task a separate task or set of tasks apply the log records from a filled buffer.
A typical log apply implementation involves 1 object at most. If the reorganization is against a table space or subset of table space partitions, then only the log records for the target table space partitions are read and applied. During the log apply, a single buffer is filled with log records and pointers to those log records. Instead of sorting actual log records, to achieve better performance, the pointers to the log records are sorted. The log is read to fill the buffer, then the log records are sorted (by pointers), the log records are updated with new RID values, the log records are sorted again, and finally the log changes are applied. This process is a single serial phase within the reorganization utility.
A large buffer, e.g., 10 MB, may be allocated for pointers to log records and the log records themselves. The area allocated for pointers is much smaller, e.g., 1/16th that allocated for log records. However, depending on the size of the log records, space is typically wasted because either the pointer area or log record area filled first, so entire buffer is not used. The space wasted in the buffer, caused either by the pointer area filling up before the log record area is full, or the log record area filling up before the pointer area is full, causes the partially full buffer to be processed while there are additional log records waiting to be read. The inefficient use of the buffer means that additional log read iterations may be required, extending the elapsed time of the reorganization.
In addition, as mentioned above, changes to the original rows occurring after being copied to the shadows must be captured by reading data changes from the log, and then applying them against the shadow data objects and related indexes. When this capture process is complete, usage can be quickly switched from the original to the reorganized shadow.
For very large amounts of data, the data may be divided amongst many partitions and it may be possible to divide indexes on the data in the same way. Data divided this way allows a subset of the data partitions to be reorganized. However, it may be necessary to have a “global” index, which covers all of the partitions for purposes of enforcing unique values or providing efficient data retrieval. This complicates the use of the shadowing technique if only a subset of the global index is shadowed and in one implementation results in the shadow subset being used to update the original global index while disallowing any access to the global index.
Another technique is to shadow the entire global index so that usage of it can be quickly switched along with the partitioned data and partitioned indexes. This requires that the log records for the remainder of the partition related entries in the global index must be processed as well as the log records for the data being reorganized. Accordingly, multiple tasks are used in conjunction with multiple buffers for retrieval and processing of the log records for the data and global index. Depending on the level of activity of manipulation of the data partitions being reorganized and the data partitions not being reorganized, the data apply tasks or the index apply tasks may complete the processing of their current buffer significantly quicker than the other set.
It can be seen that there is a need for a method, apparatus and program storage device for managing buffers during online reorganization.